<?php
//by willson 105328468@qq.com 2015/2/5
class base_model {
	public $db;
	public $ezr; //分页
	public $tbp;
	public $smarty;
	public $app, $act;
	public $ip;
	public $request;
	public $client_id;
	public $configs; //配置参数
	public $table;
	public $not_client_tables;
	public $not_access_log_tables;
	public $member_id, $member_name;

	public function __construct() {
		global $db, $ezr, $tbp, $smarty, $app, $act;
		global $request, $client_id;
		$this->db = $db;
		$this->ezr = $ezr;
		$this->tbp = $tbp;
		$this->smarty = $smarty;
		$this->app = $app;
		$this->act = $act;
		$this->ip = ip();
		$this->request = $request;
		$this->client_id = $client_id;
		
		//为SQL::share区分不需加client_id条件的表
		$this->not_client_tables = array();
		
		//不记录操作日志的表
		$this->not_access_log_tables = array(
			'log', 'collect_month'
		);
		
		//加载配置参数
		$this->configs = array();
		$_config_sql = "SELECT name, memo, content FROM {$this->tbp}config";
		$_configs = $this->db->get_results($_config_sql);
		if ($_configs) {
			foreach ($_configs as $_config) {
				$this->configs[$_config->name] = $_config->content;
			}
		}
		
		$this->member_id = 0;
		$this->member_name = '';
		if (isset($_SESSION['member'])) {
			if (property_exists($_SESSION['member'], 'id')) $this->member_id = $_SESSION['member']->id;
			if (property_exists($_SESSION['member'], 'name')) $this->member_name = $_SESSION['member']->name;
		}
	}
	
	//动态设置/获取属性
	//魔术方法，当设置的属性不存在或者不可访问(private)时就会调用此函数
	public function __set($name, $value) {
		$array = explode('_', $name);
		if (count($array)<2) return;
		$subject = $array[0];
		$property = substr($name, strlen($subject)+1);
		if (!isset($_SESSION[$subject])) $_SESSION[$subject] = new stdClass();
		$_SESSION[$subject]->$property = $value;
	}
	//魔术方法，当获取的属性不存在或者不可访问(private)时就会调用此函数
	public function __get($name) {
		$array = explode('_', $name);
		if (count($array)<2) return NULL;
		$subject = $array[0];
		$property = substr($name, strlen($subject)+1);
		if (!isset($_SESSION[$subject])) return NULL;
		return isset($_SESSION[$subject]->$property) ? $_SESSION[$subject]->$property : NULL;
	}
}

//20180108 by @jsong
class SQL extends base_model {
	private static $DB = NULL;
	private $left = array();
	private $right = array();
	private $inner = array();
	private $cross = array();
	private $where = '';
	private $order = '';
	private $group = '';
	private $isezr = false;
    private $offset = 0;
	private $pagesize = 0;
	private $isarray = false; //true:查询多行记录且只有一个字段时直接转为无索引数组
	private $debug = false; //调式模式,不执行sql
	public $log = false; //生命周期内把运行过的sql记录到log
	public $page = ''; //分页HTML代码
	public $sql = ''; //最后执行的sql
	public $sqls = array(); //生命周期内执行过的sql
	//创建单例
    public static function share($table='') {
        if (self::$DB==NULL) self::$DB = new self();
		if (strlen($table)) self::$DB->from($table);
        return self::$DB;
    }
	//生命周期内把运行过的sql记录到log
	public function log($log=true) {
		$this->log = $log;
	}
	//指定表名, 可设置别名, 如 table('table t')
    public function from($table) {
		$this->clear();
        $this->table = "{$this->tbp}{$table}";
		return $this;
    }
	//左联接
	public function left($table, $on) {
        $this->left[] = " LEFT JOIN {$this->tbp}{$table} ON {$on}";
		return $this;
    }
	//右联接
	public function right($table, $on) {
        $this->right[] = " RIGHT JOIN {$this->tbp}{$table} ON {$on}";
		return $this;
    }
	//等值联接
	public function inner($table, $on) {
        $this->inner[] = " INNER JOIN {$this->tbp}{$table} ON {$on}";
		return $this;
    }
	//多联接
	public function cross($table) {
        $this->cross[] = ",{$this->tbp}{$table}";
		return $this;
    }
	//查询条件
	public function where($where) {
		if (strtoupper(substr(strlen($where), 0, 4)) == 'AND ') $this->where .= $where;
		else $this->where .= (strlen($this->where)?' AND ':'') . $where;
		return $this;
	}
	//时间操作查询条件
	public function comparenow($interval, $field, $operatorAndValue, $now='') {
        switch ($interval) {
	        case 'y':$interval = 'YEAR';break;
	        case 'q':$interval = 'QUARTER';break;
	        case 'm':$interval = 'MONTH';break;
	        case 'w':$interval = 'WEEK';break;
	        case 'd':$interval = 'DAY';break;
	        case 'h':$interval = 'HOUR';break;
	        case 'n':$interval = 'MINUTE';break;
	        case 's':$interval = 'SECOND';break;
        }
		$interval = strtoupper($interval);
        if (!strlen($now)) $now = 'NOW()';
		$this->where .= (strlen($this->where)?' AND ':'') . "TIMESTAMPDIFF({$interval},{$field},{$now}){$operatorAndValue}";
		return $this;
	}
	//排序
    public function sort($sort) {
		return $this->order($sort);
	}
    public function order($order) {
        $this->order = "ORDER BY {$order}";
		return $this;
    }
	//分组
    public function group($group) {
        $this->group = "GROUP BY {$group}";
		return $this;
    }
	//ezr分页模式
    public function isezr($isezr=true) {
		$this->isezr = $isezr;
		return $this;
    }
    //记录偏移量
    public function offset($offset) {
        if (is_numeric($offset)) $this->offset = intval($offset);
        return $this;
    }
	//返回记录最大数目
    public function pagesize($pagesize) {
        if (is_numeric($pagesize)) $this->pagesize = intval($pagesize);
        return $this;
    }
    //设定记录偏移量与返回记录最大数目
    public function limit($offset, $pagesize) {
        $this->offset($offset);
        $this->pagesize($pagesize);
        return $this;
    }
	//查询多行记录且只有一个字段时直接转为无索引数组
	public function isarray($isarray=true) {
		$this->isarray = $isarray;
		return $this;
	}
	//不执行sql
	public function debug($debug=true) {
		$this->debug = $debug;
		return $this;
	}
	//设置分页参数
	public function setpages($data=array()) {
		$BRSR = $this->request->get('BRSR');
		$this->smarty->assign('BRSR', $BRSR);
		$this->ezr->set_qs_val('app', $this->app);
		$this->ezr->set_qs_val('act', $this->act);
		foreach ($data as $key=>$value) {
			$this->smarty->assign($key, $value);
			$this->ezr->set_qs_val($key, $value);
		}
		return $this;
	}
	//记录数量
	public function count() {
		return intval($this->val());
	}
	//记录是否存在
	public function exist() {
		return $this->count() > 0;
	}
	//查询单条记录
	public function row($field='*', $callback=NULL) {
		return $this->isezr(false)->pagesize(1)->find($field, $callback);
	}
	//查询字段值
	public function val($field='', $callback=NULL) {
		if (!strlen($field)) return intval($this->isezr(false)->pagesize(-1)->find('count(*)', $callback));
		$val = $this->isezr(false)->pagesize(-1)->find($field, $callback);
		if ($field == 'id') $val = intval($val); //默认id为数值型
		return $val;
	}
	//查询, callback为查询结果先执行一次callback,接收一个参数:查询结果
	public function find($fields='*', $callback=NULL) {
		$obj = NULL;
		if (!strlen($fields)) $fields = '*';
		$table_long = $alias = $this->table;
		if (stripos($alias, ' ')!==false) {
			$alias = explode(' ', $alias);
			$table_long = $alias[0];
			$alias = $alias[1];
		}
		$is_client_table = in_array(str_replace($this->tbp, '', $table_long), $this->not_client_tables);
		$sql = "SELECT {$fields} FROM {$this->table}";
		foreach ($this->left as $left) $sql .= $left;
		foreach ($this->right as $right) $sql .= $right;
		foreach ($this->inner as $inner) $sql .= $inner;
		foreach ($this->cross as $cross) $sql .= $cross;
		if (is_numeric($this->where)) { //默认是id
			$sql .= " WHERE ";
			if ($is_client_table) $sql .= "{$alias}.client_id='{$this->client_id}' AND ";
			$sql .= "{$alias}.id='{$this->where}'";
			if ($this->pagesize<0) {
				if (!$this->debug) $obj = $this->db->get_var($sql);
			} else {
				if (!$this->debug) $obj = $this->db->get_row($sql);
			}
		} else {
			if ($is_client_table || strlen(trim($this->where))) {
				$sql .= " WHERE ";
				if ($is_client_table) $sql .= "{$alias}.client_id='{$this->client_id}' ";
				if (strlen(trim($this->where))) {
					if ($is_client_table) {
						if (strtoupper(substr(trim($this->where), 0, 4)) == 'AND ') $sql .= trim($this->where);
						else $sql .= "AND ".trim($this->where);
					} else {
						if (strtoupper(substr(trim($this->where), 0, 4)) == 'AND ') {
							$sql .= substr(trim($this->where), 4);
						} else {
							$sql .= trim($this->where);
						}
					}
				}
			}
			if (strlen(trim($this->group))) $sql .= " {$this->group}";
			if (strlen(trim($this->order))) $sql .= " {$this->order}";
			if ($this->isezr) {
				if (!$this->debug) {
					if ($this->pagesize>0) $this->ezr->num_results_per_page = $this->pagesize;
					$obj = $this->ezr->get_results($sql);
					$obj = json_encode($obj);
					$obj = json_decode($obj); //数据结构保持与db一致
					$this->setpages($_GET);
					$this->page = $this->ezr->get_navigation();
				}
			} else {
				if ($this->pagesize<0) {
					if (!$this->debug) $obj = $this->db->get_var($sql);
				} else if ($this->pagesize==0) {
					if (!$this->debug) $obj = $this->db->get_results($sql);
					if ($this->isarray && $fields!='*' && stripos($fields,',')===false && $obj) {
						$results = array();
						foreach ($obj as $k=>$o) $results[] = $o->$fields;
						$obj = $results;
					}
				} else if ($this->pagesize==1) {
					if (!$this->debug) $obj = $this->db->get_row($sql);
				} else {
				    if ($this->offset>0) {
                        $sql .= " LIMIT {$this->offset}, {$this->pagesize}";
                    } else {
                        $sql .= " LIMIT {$this->pagesize}";
                    }
					if (!$this->debug) $obj = $this->db->get_results($sql);
					if ($this->isarray && $fields!='*' && stripos($fields,',')===false && $obj) {
						$results = array();
						foreach ($obj as $k=>$o) $results[] = $o->$fields;
						$obj = $results;
					}
				}
			}
		}
		if ($this->log) write_log($sql);
		$this->sql = $sql;
		$this->sqls[] = $sql;
		if ($obj && $callback && is_callable($callback)) $obj = $callback($obj);
		return $obj;
	}
	//插入记录, 设置array_key将以data[key]的值(数组)的数量来循环增加values
	public function insert($data, $array_key='') {
		if (!is_array($data) && !strlen(trim($data))) return false;
		$table_long = $alias = $this->table;
		if (stripos($alias, ' ')!==false) {
			$alias = explode(' ', $alias);
			$table_long = $alias[0];
			$alias = $alias[1];
		}
		$is_client_table = in_array(str_replace($this->tbp, '', $table_long), $this->not_client_tables);
		$sql = "INSERT INTO {$this->table}";
		if (is_array($data)) {
			if ($is_client_table) $data['client_id'] = $this->client_id;
			if (strlen($array_key)) {
				$array_value = isset($data[$array_key]) ? $data[$array_key] : NULL;
				if (!is_array($array_value)) $array_key = '';
			}
			$fields = '';
			$values = '';
			if (strlen($array_key)) { //多条插入
				foreach ($data as $key => $value) {
					if (strlen($fields)) {
						$fields .= ", `{$key}`";
					} else {
						$fields .= "`{$key}`";
					}
				}
				$sql .= " ({$fields}) VALUES";
				$array_values = array_values($data);
				for ($i=0; $i<count($array_value); $i++) {
					if (!strlen(trim($array_value[$i]))) continue;
					for ($j=0; $j<count($array_values); $j++) {
						if ($j==0) {
							if (is_array($array_values[$j])) {
								$values .= isset($array_values[$j][$i]) ? " ('".str_replace("'", "\'", $array_values[$j][$i])."'" : " (''";
							} else {
								$values .= " ('".str_replace("'", "\'", $array_values[$j])."'";
							}
						} else if ($j==count($array_values)-1) {
							if (is_array($array_values[$j])) {
								$values .= isset($array_values[$j][$i]) ? ", '".str_replace("'", "\'", $array_values[$j][$i])."')," : ", ''),";
							} else {
								$values .= ", '".str_replace("'", "\'", $array_values[$j])."'),";
							}
						} else {
							if (is_array($array_values[$j])) {
								$values .= isset($array_values[$j][$i]) ? ", '".str_replace("'", "\'", $array_values[$j][$i])."'" : ", ''";
							} else {
								$values .= ", '".str_replace("'", "\'", $array_values[$j])."'";
							}
						}
					}
				}
				$sql .= trim($values, ',');
			} else { //单条插入
				foreach ($data as $key => $value) {
					if (strlen($fields)) {
						$fields .= ", `{$key}`";
						$values .= ", '".str_replace("'", "\'", $value)."'";
					} else {
						$fields .= "`{$key}`";
						$values .= "'".str_replace("'", "\'", $value)."'";
					}
				}
				$sql .= " ({$fields}) VALUES ({$values})";
			}
		} else {
			$sql .= " {$data}";
		}
		if ($this->log) write_log($sql);
		$this->sql = $sql;
		$this->sqls[] = $sql;
		$insert_id = 0;
		if (!$this->debug) {
			$result = $this->db->query($sql);
			if ($result) $insert_id = intval($this->db->insert_id);
			if (stripos($_SERVER['SCRIPT_NAME'], '/gm/')!==false && $this->table=="{$this->tbp}collect") {
				$q = "SELECT * FROM {$this->tbp}collect WHERE id='{$insert_id}'";
				$row = $this->db->get_row($q);
				if ($row) {
					$row = $this->_convert_collect_log($row);
					$content = "{$this->admin_name}新增汇总表记录：".addslashes(json_encode_urlencode($row));
					$q = "INSERT INTO {$this->tbp}collect_log (admin_id, content, ip, add_time)
						VALUES ('{$this->admin_id}', '{$content}', '{$this->ip}', '".time()."')";
					$this->db->query($q);
				}
			}
		}
		return $insert_id;
	}
	//更新记录
	public function update($data) {
		if (!is_array($data) && !strlen(trim($data))) return false;
		$table_long = $alias = $this->table;
		if (stripos($alias, ' ')!==false) {
			$alias = explode(' ', $alias);
			$table_long = $alias[0];
			$alias = $alias[1];
		}
		$is_client_table = in_array(str_replace($this->tbp, '', $table_long), $this->not_client_tables);
		$where = '';
		$set = '';
		$sql = "UPDATE {$this->table} SET";
		if (is_array($data)) {
			foreach ($data as $key=>$value) {
				if (is_array($value)) { //处理类似 logins=logins+1 等, 如 'logins'=>array('logins', '+1') 或 'logins'=>array('+1') 自动引用key
					if (count($value)==1) {
						$value = "`{$key}`" . implode('', $value);
					} else {
						$value = implode('', $value);
					}
					$value = str_replace("'", "\'", $value);
				} else {
					$value = "'".str_replace("'", "\'", $value)."'";
				}
				if (strlen($set)) {
					$set .= ", `{$key}`={$value}";
				} else {
					$set .= " `{$key}`={$value}";
				}
			}
		} else {
			$set = " {$data}";
		}
		$sql .= $set;
		if (is_numeric($this->where)) {
			$q = " WHERE ";
			if ($is_client_table) $q .= "{$alias}.client_id='{$this->client_id}' AND ";
			$q .= "{$alias}.id='{$this->where}'";
			$sql .= $q;
			$where .= $q;
		} else {
			if ($is_client_table || strlen(trim($this->where))) {
				$q = " WHERE ";
				if ($is_client_table) $q .= "{$alias}.client_id='{$this->client_id}' ";
				if (strlen(trim($this->where))) {
					if ($is_client_table) {
						if (strtoupper(substr(trim($this->where), 0, 4)) == 'AND ') $q .= trim($this->where);
						else $q .= "AND ".trim($this->where);
					} else {
						if (strtoupper(substr(trim($this->where), 0, 4)) == 'AND ') {
							$q .= substr(trim($this->where), 4);
						} else {
							$q .= trim($this->where);
						}
					}
				}
				$sql .= $q;
				$where .= $q;
			}
		}
		if (strlen(trim($this->order))) {
			$q = " {$this->order}";
			$sql .= $q;
			$where .= $q;
		}
		if (strlen(trim($this->group))) {
			$q = " {$this->group}";
			$sql .= $q;
			$where .= $q;
		}
		if ($this->pagesize>0) {
			$q = " LIMIT {$this->pagesize}";
			$sql .= $q;
			$where .= $q;
		}
		if ($this->log) write_log($sql);
		$this->sql = $sql;
		$this->sqls[] = $sql;
		$result = false;
		$id = 0;
		$prev = NULL;
		if (stripos($_SERVER['SCRIPT_NAME'], '/gm/')!==false && $this->table=="{$this->tbp}collect") {
			$q = "SELECT * FROM {$this->tbp}collect";
			if (strlen($where)) $q .= " {$where}";
			$prev = $this->db->get_row($q);
			if ($prev) {
				$id = $prev->id;
				$prev = json_encode($prev);
				$prev = json_decode($prev, true);
			}
		}
		if (!$this->debug) $result = $this->db->query($sql);
		if (stripos($_SERVER['SCRIPT_NAME'], '/gm/')!==false && $this->table=="{$this->tbp}collect") {
			$q = "SELECT * FROM {$this->tbp}collect WHERE id='{$id}'";
			$row = $this->db->get_row($q);
			if ($row) {
				$row = json_encode($row);
				$row = json_decode($row, true);
				$prevrow = array_diff_assoc($prev, $row);
				$row = array_diff_assoc($row, $prev);
				$prevrow = $this->_convert_collect_log($prevrow);
				$row = $this->_convert_collect_log($row);
				$content = "{$this->admin_name}更新汇总表记录：id{$id}，原数据<font color=\"gray\">".addslashes(json_encode_urlencode($prevrow))."</font><br /><font color=\"gray\">改为".addslashes(json_encode_urlencode($row))."</font>";
				$q = "INSERT INTO {$this->tbp}collect_log (admin_id, content, ip, add_time)
					VALUES ('{$this->admin_id}', '{$content}', '{$this->ip}', '".time()."')";
				$this->db->query($q);
			}
		}
		return $result;
	}
	//删除记录
	public function delete($where='') {
		$table_long = $alias = $this->table;
		if (stripos($alias, ' ')!==false) {
			$alias = explode(' ', $alias);
			$table_long = $alias[0];
			$alias = $alias[1];
		}
		$is_client_table = in_array(str_replace($this->tbp, '', $table_long), $this->not_client_tables);
		if (strlen($where)) $this->where($where);
		$where = '';
		$sql = "DELETE FROM {$this->table}";
		if (is_numeric($this->where)) {
			$q = " WHERE ";
			if ($is_client_table) $q .= "{$alias}.client_id='{$this->client_id}' AND ";
			$q .= "{$alias}.id='{$this->where}'";
			$sql .= $q;
			$where .= $q;
		} else {
			if ($is_client_table || strlen(trim($this->where))) {
				$q = " WHERE ";
				if ($is_client_table) $q .= "{$alias}.client_id='{$this->client_id}' ";
				if (strlen(trim($this->where))) {
					if ($is_client_table) {
						if (strtoupper(substr(trim($this->where), 0, 4)) == 'AND ') $q .= trim($this->where);
						else $q .= "AND ".trim($this->where);
					} else {
						if (strtoupper(substr(trim($this->where), 0, 4)) == 'AND ') {
							$q .= substr(trim($this->where), 4);
						} else {
							$q .= trim($this->where);
						}
					}
				}
				$sql .= $q;
				$where .= $q;
			}
		}
		if (strlen(trim($this->order))) {
			$q = " {$this->order}";
			$sql .= $q;
			$where .= $q;
		}
		if (strlen(trim($this->group))) {
			$q = " {$this->group}";
			$sql .= $q;
			$where .= $q;
		}
		if ($this->pagesize>0) {
			$q = " LIMIT {$this->pagesize}";
			$sql .= $q;
			$where .= $q;
		}
		if ($this->log) write_log($sql);
		$this->sql = $sql;
		$this->sqls[] = $sql;
		if (stripos($_SERVER['SCRIPT_NAME'], '/gm/')!==false && $this->table=="{$this->tbp}collect") {
			$q = "SELECT * FROM {$this->tbp}collect";
			if (strlen($where)) $q .= " {$where}";
			$row = $this->db->get_row($q);
			if ($row) {
				$row = $this->_convert_collect_log($row);
				$content = "{$this->admin_name}删除汇总表记录：".addslashes(json_encode_urlencode($row));
				$q = "INSERT INTO {$this->tbp}collect_log (admin_id, content, ip, add_time)
					VALUES ('{$this->admin_id}', '{$content}', '{$this->ip}', '".time()."')";
				$this->db->query($q);
			}
		}
		$result = false;
		if (!$this->debug) $result = $this->db->query($sql);
		return $result;
	}
	//自执行SQL
	public function query($sql) {
		$this->sql = $sql;
		$this->sqls[] = $sql;
		$result = false;
		if (!$this->debug) {
			if (preg_match("/^INSERT\s+/i", $sql)) {
				$this->db->query($sql);
				return intval($this->db->insert_id);
			}
			$result = $this->db->query($sql);
		}
		if ($this->log) write_log($sql);
		return $result;
	}
	//清空参数
	public function clear() {
		$this->left = array();
		$this->right = array();
		$this->inner = array();
		$this->where = '';
		$this->order = '';
		$this->group = '';
		$this->isezr = false;
        $this->offset = 0;
        $this->pagesize = 0;
		$this->isarray = false;
		$this->debug = false;
		$this->page = '';
		$this->sql = '';
		//$this->sqls = array();
	}
	//输出所有信息
	public function info() {
		print '<pre>';
		print_r($this);
		print '</pre>';
		exit;
	}

	private function _convert_collect_log($row){
		$fields = array('city_id'=>'城市id',
			'member_type'=>'会员类型',
			'member_id'=>'会员id',
			'member_name'=>'会员账号',
			'begin_time'=>'首次缴费日期',
			'end_time'=>'到期日期',
			'real_name'=>'姓名',
			'idcard'=>'身份证',
			'social_number'=>'社保号',
			'gjj_number'=>'公积金账号',
			'person_type'=>'户口性质',
			'mobile'=>'联系电话',
			'casy_id'=>'社保档次',
			'social_price'=>'社保费用',
			'gjj_price'=>'公积金费用',
			'service_price'=>'社保服务费',
			'gjj_service_price'=>'公积金服务费',
			'total_price'=>'合计费用',
			'admin_id'=>'跟进人id',
			'month'=>'社保缴费月份',
			'gmonth'=>'公积金缴费月份',
			'remark1'=>'备注1',
			'remark2'=>'备注2',
			'status'=>'社保增员状态',
			'kstatus'=>'社保扣费状态',
			'gstatus'=>'公积金增员状态',
			'gkstatus'=>'公积金扣费状态',
			'add_time'=>'添加日期',
		);
		$new_row = array();
		// $new_row = new stdClass;
		// if (is_array($row)) {
			foreach ($row as $field => $value) {
				foreach ($fields as $field2 => $name) {
					// echo "{$field2}={$field}:{$name}<br>";
					if ($field2==$field) {
						$new_row[$name] = $value;
					}
				}
			}
		// }
		return $new_row;
	}
}
